"""
Case Type   : GSC功能模块
Case Name   : 宽表（包含多个不同数据类型）进行gsc缓存功能验证
Create At   : 2024/10/26
Owner       : @lonely-dance
Description :
    1.配置数据库参数
    2.重启数据库生效
    3.创建宽表，并插入1条数据
    4.查看syscache占用情况
    5.增加外表索引
    6.查看syscache占用情况
    7.删除表，查看syscache占用情况
    8.循环进行表删除创建插入操作
    9.查看syscache占用情况
    10.清理环境
Expect      :
    1.修改参数成功
    2.重启成功
    3.执行成功
    4.查寻成功
    5.成功
    6.查寻成功
    7.删除成功，查寻成功
    8.执行成功
    9.查寻成功
    10.成功
History     :
"""
import os
import unittest

from testcase.utils.ComThread import ComThread
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class GscTestCase(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.tb_name = 'tb_gsc0066'
        self.com = Common('PrimaryDbUser')
        self.constant = Constant()
        self.p_node = Node('PrimaryDbUser')
        self.init_para1 = None
        self.ass1 = True
        self.ass2 = False

    def test_GSC(self):
        text = '----step1:配置数据库参数;----'
        self.log.info(text)
        status_result = self.pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + text)
        text = '----查看初始配置值;----'
        self.log.info(text)
        self.init_para1 = self.com.show_param('enable_global_syscache')
        self.log.info(self.init_para1)

        result = self.pri_sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_global_syscache=on")
        self.log.info(result)
        self.assertTrue(result, '执行失败:' + text)

        text = '----step2:重启数据库生效; expect:重启成功----'
        self.log.info(text)
        restart = self.pri_sh.restart_db_cluster()
        self.assertTrue(restart, '执行失败:' + text)

        text = f'----step3:创建宽表，并插入1条数据; expect:执行成功----'
        self.log.info(text)
        cmd = (f"\n"
               f"            drop table if exists {self.tb_name};\n"
               f"            create table {self.tb_name} (col1 money,  col2 boolean, col3 CHAR(4), \n"
               f"            col4 CHARACTER(4), col5 NCHAR(4), col6 VARCHAR(10), col7 CHARACTER VARYING(10), \n"
               f"            col8 VARCHAR2(10), col9 NVARCHAR2(10), col10 TEXT, col11 CLOB, col12 BLOB, \n"
               f"            col13 RAW, col14 BYTEA, col15 DATE, col16 TIME, col17 time with time zone, col18 timestamp, \n"
               f"            col19 timestamp with time zone, col20 SMALLDATETIME, col21 abstime, col22 cidr, col23 inet, \n"
               f"            col24 macaddr, col25 BIT(3), col26 BIT VARYING(5), col27 UUID, col28 tsvector, col29 tsquery, \n"
               f"            set hll(14), col33 int4range, col34 int8range, col35 numrange, col36 tsrange, col37 tstzrange, \n"
               f"            col38 daterange, col39 HASH16, col40 HASH32); \n"
               f"            insert into {self.tb_name} values ('52093.89'::money, true, 'aa', \n"
               f"            'aa', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', empty_blob(), HEXTORAW('DEADBEEF'), E'\\xDEADBEEF', \n"
               f"            '1900-01-01 00:00:00', '1900-01-01 00:00:00 pst', '1900-01-01 00:00:00', '1900-01-01 00:00:00 pst', \n"
               f"            '1900-01-01 00:00:00', '1900-01-01 00:00:00', '1910-01-01 00:00:00', '10.10.10.10', '10.10.10.10', \n"
               f"            '08002b010203', B'10'::bit(3), B'00', 'a0eebc999c0b4ef8bb6d6bb9bd380a11', 'test', 'test', \n"
               f"            hll_empty(14,-1), '[1,10]', '[1,10]', '[0.00, 10.00]', \n"
               f"            '[1900-01-01 00:00:00, 2020-01-01 00:00:00]', '[1900-01-01 00:00:00 pst, 2020-01-01 00:00:00 pst]', \n"
               f"            '(1900-01-01, 2020-01-01)','ffff', 'ffffffffffffffffffffffffffffffff');\n"
               f"        ")
        self.log.info(cmd)
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res, '执行失败:' + text)
        self.assertIn('INSERT 0 1', res, '执行失败:' + text)

        text = '----step4:查看syscache占用情况; expect:查询成功----'
        self.log.info(text)
        cmd = f"""
            select * from gs_gsc_table_detail() where relname like '{self.tb_name}%';
        """
        self.log.info(cmd)
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(f'{self.tb_name}', res, '执行失败:' + text)

        text = '----step5:增加外表索引; expect:成功----'
        self.log.info(text)
        cmd = f"""
            create index on {self.tb_name}(col1);
        """
        self.log.info(cmd)
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS, res, '执行失败:' + text)

        text = '----step6:查看syscache占用情况; expect:查询成功----'
        self.log.info(text)
        cmd = f"""
            select * from gs_gsc_table_detail() where relname like '{self.tb_name}%';
        """
        self.log.info(cmd)
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn('(0 rows)', res, '执行失败:' + text)

        text = '----step7:删除表后查询syscache占用情况; expect:成功----'
        self.log.info(text)
        cmd = f"drop table if exists {self.tb_name};" \
            f"select * from gs_gsc_table_detail() where relname like '{self.tb_name}%';"
        self.log.info(cmd)
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, '执行失败:' + text)
        self.assertIn('(0 rows)', res, '执行失败:' + text)

        text = '----step8:循环进行表删除创建插入操作; expect:成功----'
        self.log.info(text)
        cmd = (f"\n"
               f"            drop table if exists {self.tb_name};\n"
               f"            create table {self.tb_name} (col1 money,  col2 boolean, col3 CHAR(4), \n"
               f"            col4 CHARACTER(4), col5 NCHAR(4), col6 VARCHAR(10), col7 CHARACTER VARYING(10), \n"
               f"            col8 VARCHAR2(10), col9 NVARCHAR2(10), col10 TEXT, col11 CLOB, col12 BLOB, \n"
               f"            col13 RAW, col14 BYTEA, col15 DATE, col16 TIME, col17 time with time zone, col18 timestamp, \n"
               f"            col19 timestamp with time zone, col20 SMALLDATETIME, col21 abstime, col22 cidr, col23 inet, \n"
               f"            col24 macaddr, col25 BIT(3), col26 BIT VARYING(5), col27 UUID, col28 tsvector, col29 tsquery, \n"
               f"            set hll(14), col33 int4range, col34 int8range, col35 numrange, col36 tsrange, col37 tstzrange, \n"
               f"            col38 daterange, col39 HASH16, col40 HASH32); \n"
               f"            insert into {self.tb_name} values ('52093.89'::money, true, 'aa', \n"
               f"            'aa', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', empty_blob(), HEXTORAW('DEADBEEF'), E'\\xDEADBEEF', \n"
               f"            '1900-01-01 00:00:00', '1900-01-01 00:00:00 pst', '1900-01-01 00:00:00', '1900-01-01 00:00:00 pst', \n"
               f"            '1900-01-01 00:00:00', '1900-01-01 00:00:00', '1910-01-01 00:00:00', '10.10.10.10', '10.10.10.10', \n"
               f"            '08002b010203', B'10'::bit(3), B'00', 'a0eebc999c0b4ef8bb6d6bb9bd380a11', 'test', 'test', \n"
               f"            hll_empty(14,-1), '[1,10]', '[1,10]', '[0.00, 10.00]', \n"
               f"            '[1900-01-01 00:00:00, 2020-01-01 00:00:00]', '[1900-01-01 00:00:00 pst, 2020-01-01 00:00:00 pst]', \n"
               f"            '(1900-01-01, 2020-01-01)','ffff', 'ffffffffffffffffffffffffffffffff');\n"
               f"            select * from {self.tb_name};\n"
               f"        ")
        thread_1 = ComThread(self.pri_sh.cycle_exec_sql, args=(cmd, 1000, False, None, True))
        thread_1.setDaemon(True)
        thread_1.start()
        thread_1.join(300)

        text = '----step9:循环查看syscache占用情况; expect:成功----'
        self.log.info(text)
        cmd = f"select * from gs_gsc_table_detail() where relname like '{self.tb_name}%';"
        self.log.info(cmd)
        thread_2 = ComThread(self.pri_sh.cycle_exec_sql, args=(cmd, 1000, False, None, True))
        thread_2.setDaemon(True)
        thread_2.start()
        thread_2.join(300)

        res1 = thread_1.get_result()
        res2 = thread_2.get_result()
        self.log.info(res1)
        self.log.info(res2)
        for res in res1:
            if res.endswith('ERROR'):
                self.ass1 = False
                return self.ass1
        self.assertTrue(self.ass1, '执行失败:' + text)
        for res in res2:
            if res.endswith('(1 row)'):
                self.ass2 = True
                return self.ass2
        self.assertTrue(self.ass1, '执行失败:' + text)
        self.assertTrue(self.ass2, '执行失败:' + text)

    def tearDown(self):
        text = '----step10:清理环境; expect:成功----'
        self.log.info(text)
        cmd = f"""
            drop table if exists {self.tb_name};
        """
        res = self.pri_sh.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, '执行失败:' + text)

        text = '----恢复数据库参数----'
        self.log.info(text)
        result = self.pri_sh.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f"enable_global_syscache={self.init_para1}")
        self.assertTrue(result, '执行失败:' + text)

        text = '----重启数据库----'
        self.log.info(text)
        restart = self.pri_sh.restart_db_cluster()
        self.assertTrue(restart, '执行失败:' + text)

        self.log.info(f'----{os.path.basename(__file__)} end----')
